package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IFixInfo;
import youleche.com.model.MFixInfo;

/**
 * 修理信息数据访问层
 * @author Administrator
 *
 */
public class DFixInfo implements IFixInfo{
	/**
	 * 添加修理I信息
	 * @param con 数据库连接
	 * @param fixInfo 修理信息
	 * @return 返回添加影响的行数
	 * @throws Exception 异常 
	 */
	@Override
	public int addFixInfo(Connection con, MFixInfo fixInfo) throws Exception {
		PreparedStatement pre = null;
		try{
			String sql = "INSERT fixInfo(fixId,shopName,isGoHome,fixTime,linkMan,phoneNum,fixTitle,fixContent,sellerId,isDelete,addTime,A1,A2,A3,A4,A5,A6,A7,A8,A9) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			pre = con.prepareStatement(sql);
			pre.setObject(1, fixInfo.getFixId());
			pre.setObject(2, fixInfo.getShopName());
			pre.setObject(3, fixInfo.getIsGoHome());
			pre.setObject(4, fixInfo.getFixTime());
			pre.setObject(5, fixInfo.getLinkMan());
			pre.setObject(6, fixInfo.getPhoneNum());
			pre.setObject(7, fixInfo.getFixTitle());
			pre.setObject(8, fixInfo.getFixContent());
			pre.setObject(9, fixInfo.getSellerId());
			pre.setObject(10, fixInfo.getIsDelete());
			pre.setObject(11, fixInfo.getAddTime());
			pre.setObject(12, fixInfo.getA1());
			pre.setObject(13, fixInfo.getA2());
			pre.setObject(14, fixInfo.getA3());
			pre.setObject(15, fixInfo.getA4());
			pre.setObject(16, fixInfo.getA5());
			pre.setObject(17, fixInfo.getA6());
			pre.setObject(18, fixInfo.getA7());
			pre.setObject(19, fixInfo.getA8());
			pre.setObject(20, fixInfo.getA9());

			return pre.executeUpdate();
		}finally{
			JdbcHelper.free(null, pre);
		}
	}

	/**
	 * 获取修理信息
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 结束索引
	 * @return 返回修理信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MFixInfo> getFixInfos(Connection con, String startIndex,
			String endIndex) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"fixId,shopName,isGoHome,fixTime,linkMan," +
					"phoneNum,fixTitle,fixContent,sellerId," +
					"addTime," +
					"A1,A2,A3,A4,A5," +
					"A6,A7,A8,A9 " +
					"FROM fixInfo " +
					"where " +
					"isDelete='0' " +
					"order by addTime desc limit "+startIndex+","+endIndex+";";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MFixInfo fix = null;
			List<MFixInfo> list = null;
			if(rs != null){
				list = new LinkedList<MFixInfo>();
				while(rs.next()){
					fix = new MFixInfo();
					fix.setFixId(rs.getString(1));
					fix.setShopName(rs.getString(2));
					fix.setIsGoHome(rs.getString(3));
					fix.setFixTime(rs.getString(4));
					fix.setLinkMan(rs.getString(5));
					fix.setPhoneNum(rs.getString(6));
					fix.setFixTitle(rs.getString(7));
					fix.setFixContent(rs.getString(8));
					fix.setSellerId(rs.getString(9));
					fix.setAddTime(rs.getDate(10));
					fix.setA1(rs.getString(11));
					fix.setA2(rs.getString(12));
					fix.setA3(rs.getString(13));
					fix.setA4(rs.getString(14));
					fix.setA5(rs.getString(15));
					fix.setA6(rs.getString(16));
					fix.setA7(rs.getString(17));
					fix.setA8(rs.getString(18));
					fix.setA9(rs.getString(19));
					list.add(fix);
				}
			}
				
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 用户置顶修理信息
	 * @param con 数据库连接
	 * @param fixId 修理信息ID
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int topFixInfoById(Connection con, String fixId) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql ="update fixInfo set addTime = now() where fixId='"+fixId+"' and isDelete='0' limit 1;";
		
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 根据ID得到修理信息
	 * @param con 数据库连接
	 * @param fixId 修理信息ID
	 * @return 返回修理信息
	 * @throws Exception 异常
	 */
	@Override
	public MFixInfo getFixInfoById(Connection con, String fixId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"fixId,shopName,isGoHome,fixTime,linkMan," +
					"phoneNum,fixTitle,fixContent,sellerId," +
					"addTime," +
					"A1,A2,A3,A4,A5," +
					"A6,A7,A8,A9 " +
					"FROM fixInfo " +
					"where " +
					"isDelete='0' and fixId='"+fixId+"' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MFixInfo fix = null;
			if(rs != null){
				while(rs.next()){
					fix = new MFixInfo();
					fix.setFixId(rs.getString(1));
					fix.setShopName(rs.getString(2));
					fix.setIsGoHome(rs.getString(3));
					fix.setFixTime(rs.getString(4));
					fix.setLinkMan(rs.getString(5));
					fix.setPhoneNum(rs.getString(6));
					fix.setFixTitle(rs.getString(7));
					fix.setFixContent(rs.getString(8));
					fix.setSellerId(rs.getString(9));
					fix.setAddTime(rs.getDate(10));
					fix.setA1(rs.getString(11));
					fix.setA2(rs.getString(12));
					fix.setA3(rs.getString(13));
					fix.setA4(rs.getString(14));
					fix.setA5(rs.getString(15));
					fix.setA6(rs.getString(16));
					fix.setA7(rs.getString(17));
					fix.setA8(rs.getString(18));
					fix.setA9(rs.getString(19));
					break;
				}
			}
				
			return fix;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 得到用户的修理信息总数
	 * @param con 数据库连接
	 * @param sellerId 用户ID
	 * @return 返回修理信息总数
	 * @throws Exception
	 */
	@Override
	public String getFixInfoCountBySellerId(Connection con, String sellerId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(fixId) from fixInfo where sellerId='"+sellerId+"' and isDelete ='0';";

			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					return rs.getString(1);
				}
			}
			
			return "0";
		} finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 根据用户Id获取修理信息
	 * @param con 数据库连接
	 * @param startIndex 开始索引
	 * @param endIndex 结束索引
	 * @param sellerId 用户ID
	 * @return 返回修理信息
	 * @throws Exception 异常
	 */
	@Override
	public List<MFixInfo> getFixInfosBySellerId(Connection con, String startIndex,
			String endIndex, String sellerId) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"fixId,shopName,isGoHome,fixTime,linkMan," +
					"phoneNum,fixTitle,fixContent,sellerId," +
					"addTime," +
					"A1,A2,A3,A4,A5," +
					"A6,A7,A8,A9 " +
					"FROM fixInfo " +
					"where " +
					"isDelete='0' and sellerId='"+sellerId+"'" +
					"order by addTime desc limit "+startIndex+","+endIndex+";";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			MFixInfo fix = null;
			List<MFixInfo> list = null;
			if(rs != null){
				list = new LinkedList<MFixInfo>();
				while(rs.next()){
					fix = new MFixInfo();
					fix.setFixId(rs.getString(1));
					fix.setShopName(rs.getString(2));
					fix.setIsGoHome(rs.getString(3));
					fix.setFixTime(rs.getString(4));
					fix.setLinkMan(rs.getString(5));
					fix.setPhoneNum(rs.getString(6));
					fix.setFixTitle(rs.getString(7));
					fix.setFixContent(rs.getString(8));
					fix.setSellerId(rs.getString(9));
					fix.setAddTime(rs.getDate(10));
					fix.setA1(rs.getString(11));
					fix.setA2(rs.getString(12));
					fix.setA3(rs.getString(13));
					fix.setA4(rs.getString(14));
					fix.setA5(rs.getString(15));
					fix.setA6(rs.getString(16));
					fix.setA7(rs.getString(17));
					fix.setA8(rs.getString(18));
					fix.setA9(rs.getString(19));
					list.add(fix);
				}
			}
				
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

}
